package com.lr.excel.service.impl;

import com.lr.excel.entity.ExcelData;
import com.lr.excel.dao.ExcelDataDao;
import com.lr.excel.service.ExcelDataService;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * (ExcelData)表服务实现类
 *
 * @author makejava
 * @since 2024-06-13 22:16:03
 */
@Service("excelDataService")
public class ExcelDataServiceImpl implements ExcelDataService {
    @Resource
    private ExcelDataDao excelDataDao;


    @Override
    public int insert(ExcelData excelData) {
        return excelDataDao.insert(excelData);
    }

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<ExcelData> 实例对象列表
     * @return 影响行数
     */
    @Override
    public int insertBatch(List<ExcelData> entities) {
        return excelDataDao.insertBatch(entities);
    }

    private void importDataFromExcelWithEasyExcel() {

    }

    private void importDataFromExcelWithSXSS() {
        long startTime = System.currentTimeMillis();
        String excelFilePath = "million_rows_data.xlsx";
        int batchSize = 1000; // 每次批量插入的记录数


        try {
            FileInputStream inputStream = new FileInputStream(excelFilePath);
            // 在读取Excel文件之前，设置新的最大字节数限制
            int newLimit = 200_000_000; // 例如，设置为200MB
            IOUtils.setByteArrayMaxOverride(newLimit);
            SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(inputStream), 100);
            XSSFWorkbook xssfWorkbook = workbook.getXSSFWorkbook();
            Sheet sheet = xssfWorkbook.getSheetAt(0);
            if (sheet == null) {
                System.out.println("No sheet found in the workbook.");
                return;
            }
            List<ExcelData> batchList = new ArrayList<>();
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                ExcelData dataRow = new ExcelData();
                dataRow.setUuid(row.getCell(0).getStringCellValue());

                for (int j = 1; j <= 20; j++) {
                    // 反射方式读取
                    setFieldByIndex("randomText" + j, row.getCell(j).getStringCellValue(), dataRow);
                }

                batchList.add(dataRow);

                // 当达到批量大小时，执行批量插入
                if (batchList.size() == batchSize) {
                    excelDataDao.insertBatch(batchList); // 假设有一个批量插入的方法
                    batchList.clear(); // 清空批量列表
                }
            }

            // 插入剩余的记录
            if (!batchList.isEmpty()) {
                excelDataDao.insertBatch(batchList);
            }
            workbook.close();

        } catch (Exception e) {

            e.printStackTrace();
        }

        System.out.println("导入100万条数据到MySQL所需时间：" + (System.currentTimeMillis() - startTime) + "ms");
    }


    public void importDataFromExcelWithXSS1() {
        long l = System.currentTimeMillis();
        FileInputStream inputStream = null;
        Workbook workbook = null;
        String excelFilePath = "million_rows_data.xlsx";
        try {
            inputStream = new FileInputStream(excelFilePath);
            // 在读取Excel文件之前，设置新的最大字节数限制
            int newLimit = 200_000_000; // 例如，设置为200MB
            IOUtils.setByteArrayMaxOverride(newLimit);
            int windowSize = 100; // Adjust this based on your available memory
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                ExcelData dataRow = new ExcelData();
                dataRow.setUuid(row.getCell(0).getStringCellValue());

                for (int j = 1; j <= 20; j++) {
                    //反射方式读取
                    setFieldByIndex("randomText" + j, row.getCell(j).getStringCellValue(), dataRow);
                }

                excelDataDao.insert(dataRow);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                inputStream.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

        System.out.println("导入100万条数据到mysql的所需要的时间为：" + (System.currentTimeMillis() - l) + "ms");

    }

    /**
     * 动态设置字段值
     *
     * @param fieldName 字段名，如"randomText1"
     * @param value     要设置的值
     */
    private void setFieldByIndex(String fieldName, String value, ExcelData dataRow) {
        try {
            java.lang.reflect.Field field = dataRow.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(dataRow, value);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
